-- created by kumiler
-- on 2023/1/6 15:20
-- DESC

insert overwrite table spmi_dim.dim_spmi_pcp_waybill_mt partition (mt)
select id,
       waybill_month,
       send_financial_center_name,
       send_financial_center_code,
       policy_name,
       send_network_name,
       send_network_code,
       subsidy,
       destination_name,
       destination_code,
       create_time,
       date_flag,
       fee_type_code               as policy_code,
       substr(waybill_month, 1, 7) as mt
from (
         select *,
                2 as                                                                        date_flag,
                rank() over (partition by waybill_month order by to_date(create_time) desc) time_rank
         from spmi_ods.spmi_pcp_waybill_enter
         where dt = '{{ execution_date | cst_ds }}'

         union all

         select *,
                1 as                                                                        date_flag,
                rank() over (partition by waybill_month order by to_date(create_time) desc) time_rank
         from spmi_ods.spmi_pcp_waybill_input
         where dt = '{{ execution_date | cst_ds }}'
     ) tmp
         left join
     (select DISTINCT fee_type_code, fee_type_name
      from spmi_ods.spmn_pcp_config_relation
      where dt = '{{ execution_date | cst_ds }}') tmp1
     on tmp.policy_name = tmp1.fee_type_name
where time_rank = 1
;